package com.dao.impl;

import com.bean.Repair;
import com.dao.RepairDAO;
import com.util.C3p0Utils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class RepairDAOImpl implements RepairDAO {

    /**
     * BeanListHandler将结果列表封装
     * @return
     * @throws SQLException
     */
    @Override
    public List<Repair> selectAll() throws SQLException {

        return C3p0Utils.qr.query("select * from repairmsg", new BeanListHandler<Repair>(Repair.class));
    }
    /**
     * BeanHandler将单个列表封装
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Repair selectById(Integer id) throws SQLException {
        return C3p0Utils.qr.query("select * from repairmsg  where repairId =?", new BeanHandler<>(Repair.class), id);
    }
    @Override
    public List<Repair> selectByName(String ownerId) throws SQLException {
        return C3p0Utils.qr.query("select * from repairmsg where ownerId =?", new BeanListHandler<>(Repair.class), ownerId);
    }
    @Override
    public boolean deleteById(Integer id) throws SQLException {
        return C3p0Utils.qr.update("delete from repairmsg where repairId=?", id) > 0;
    }
    @Override
    public boolean deleteAll(String[] idsArr) throws SQLException {
        StringBuffer sql = new StringBuffer("delete from repairmsg where repairId in (");
        for (int i = 0; i < idsArr.length; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        return C3p0Utils.qr.update(sql.toString(), idsArr) > 0;
    }
    @Override
    public boolean insert(Repair r) throws SQLException {
        return C3p0Utils.qr.update("insert into repairmsg values(null,?,?,?,?,?,?,?,?,?,?)", r.getOwnerId(),
                r.getRepairPhone(),r.getRepairAddr(),r.getRepairState(),r.getRepairCreatetime(),
                r.getRepairEndtime(),r.getRepairContents(), r.getRepairPleased(),r.getServiceAssess(),r.getEmployeeId()) > 0;
    }
    @Override
    public boolean update(Repair r) throws SQLException {
        return C3p0Utils.qr.update("update repairmsg set ownerId=?,repairPhone=?,repairAddr=?,repairState=?,repairCreatetime=?," +
                        "repairEndtime=?,repairContents=?,repairPleased=?,serviceAssess=?,employeeId=? where repairId=?",
                r.getOwnerId(),r.getRepairPhone(),r.getRepairAddr(),r.getRepairState(),r.getRepairCreatetime(),
                r.getRepairEndtime(),r.getRepairContents(), r.getRepairPleased(),r.getServiceAssess(),r.getEmployeeId(),r.getRepairId()) > 0;
    }

    /**
     * ScalarHandler用来将单个Object类型封装
     * @return
     * @throws SQLException
     */
    @Override
    public Integer count() throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from repairmsg", new ScalarHandler<Long>()).intValue();
        return n;
    }
    @Override
    public Integer countSearch(Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from repairmsg where 1=1");
        if(map==null){
            return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>()).intValue();
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append(" and "+key+" like ?");
            list.add("%"+value+"%");
        }
        return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>(),list.toArray()).intValue();
    }
    @Override
    public List<Repair> selectAllPagination(Integer offset, Integer limit) throws SQLException {
        return C3p0Utils.qr.query("select * from repairmsg limit ?,?", new BeanListHandler<>(Repair.class), offset, limit);

    }
    @Override
    public List<Repair> selectAllPaginationSearch(Integer offset, Integer limit, Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from repairmsg where 1=1");
        if(map==null){
            sql.append(" limit ?,?");
            return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Repair.class),offset,limit);
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append(" and "+key+" like ?");

            list.add("%"+value+"%");
        }
        sql.append(" limit ?,?");
        list.add(offset);
        list.add(limit);
        return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Repair.class),list.toArray());

    }

}
